package Model;

import Control.Car;
import Control.MyRunnable;

import java.sql.*;
import java.util.HashSet;

public class DBUtil {
    //新建获取当前系统时间的变量:数据库的时间类
    public static Time currentDate;
    private Connection connection;
    private static DBUtil dbUtil;

    public DBUtil() {
        try {
            //加载数据库驱动
            Class.forName("org.sqlite.JDBC");
            //连接数据库
            connection = DriverManager.getConnection("jdbc:sqlite:CarDatabase");
        } catch (ClassNotFoundException e) {
            System.out.println("error:数据库驱动加载失败！");
        } catch (SQLException e) {
            System.out.println("error:连接数据库失败！");
        }
    }

    //单例数据库对象设计
    public static DBUtil getDbUtil(){
        if (dbUtil==null){
            dbUtil = new DBUtil();
        }
        return dbUtil;
    }

    //得到数据库的数据并添加到集合中
    public static void getData(HashSet<Car> carHashSet){
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet result = null;
        try {
            //获取数据库连接
            connection = DBUtil.getDbUtil().connection;
            //得到查询语句
            pstmt = connection.prepareStatement("SELECT id,timein,timeout,state FROM carTable");
            //执行查询语句返回结果
            result = pstmt.executeQuery();
            //将相应的数据加到集合中
            while (result.next()){
                carHashSet.add(new Car(result.getString("id")
                        ,result.getString("timein")
                        ,result.getString("timeout")
                        ,result.getString("state")));
            }
        } catch (Exception e){
            System.out.println("数据库查询语句或者数据库数据错误");
            e.printStackTrace();
        }
    }

    //获取识别的结果,通过判断carInt的值：0为当前入场的车辆，1为出场的车辆
    public static String getCarResult(int carInt){
        String currentCar="null";
        try {
            //获取数据库连接
            Connection connection = DBUtil.getDbUtil().connection;
            //得到查询语句
            PreparedStatement pstmt = connection.prepareStatement("SELECT carState,carName FROM carState");
            //执行查询语句返回结果
            ResultSet result = pstmt.executeQuery();
            //获取车牌
            while (result.next()){
                currentCar = result.getString("carName");
            }
            //删除该条数据库的记录
            pstmt = connection.prepareStatement("DELETE FROM carState WHERE carState=? AND carName=?");
            pstmt.setInt(1,carInt);
            pstmt.setString(2,currentCar);
            System.out.println("成功删除："+pstmt.executeUpdate()+"数据");
            //返回当前的车牌信息
            return currentCar;
        } catch (Exception e){
            System.out.println("数据库查询语句或者数据库数据错误");
            e.printStackTrace();
        }
        return currentCar;
    }

    //将数据添加到数据库,并返回一个整形判断是否添加成功0为失败，1为成功
    public static int addCarTable(String currentCar){
        try {
            //获取数据库连接
            Connection connection = DBUtil.getDbUtil().connection;
            //得到查询语句
            PreparedStatement pstmt = connection.prepareStatement("INSERT INTO carTable(id,timein,state) VALUES (?,?,?)");
            //获取系统的当前时间
            currentDate = new Time(System.currentTimeMillis());
            System.out.println("当前系统时间："+currentDate);
            //执行查询语句返回结果
            pstmt.setString(1,currentCar);
            pstmt.setString(2,currentDate.toString());
            pstmt.setString(3,"车辆进入");
            //返回修改的数据行数来判断是否有添加进数据库成功
            int i = pstmt.executeUpdate();
            return i;
        } catch (Exception e){
            System.out.println("数据库查询语句或者数据库数据错误");
            return 0;
        }
    }

    //车辆出场时更新数据库数据
    public static int updateOutData(String carID){
        String currentCar="null";
        try {
            //获取数据库连接
            Connection connection = DBUtil.getDbUtil().connection;
            //得到查询语句
            PreparedStatement pstmt = connection.prepareStatement("UPDATE carTable SET timeout = ? , state = ?" +
                    "WHERE id = ?");
            //获取系统的当前时间
            currentDate = new Time(System.currentTimeMillis());
            System.out.println("当前系统时间："+currentDate);
            //执行查询语句返回结果
            pstmt.setString(1,currentDate.toString());
            pstmt.setString(2,"车辆出场");
            pstmt.setString(3,carID);
            int result = pstmt.executeUpdate();
            //更新显示的数据
            MyRunnable.clearCarHashSet();
            return result;
        } catch (Exception e){
            System.out.println("数据库查询语句或者数据库数据错误");
            return 0;
        }
    }

    //将数据添加到历史记录的表中,并返回一个整形判断是否添加成功0为失败，1为成功
    public static int addCarHistory(String currentCar){
        try {
            //获取数据库连接
            Connection connection = DBUtil.getDbUtil().connection;
            //得到查询语句
            PreparedStatement pstmt = connection.prepareStatement("SELECT id,timein,timeout,state FROM carTable " +
                    "WHERE id = ?");
            pstmt.setString(1,currentCar);
            //执行查询语句返回结果
            ResultSet result = pstmt.executeQuery();
            System.out.println("时间:"+result.getString("timein"));
            pstmt = connection.prepareStatement("INSERT INTO carHistory(id,timein,timeout,state) VALUES (?,?,?,?)");
            pstmt.setString(1,result.getString("id"));
            pstmt.setString(2,result.getString("timein"));
            pstmt.setString(3,result.getString("timein"));
            pstmt.setString(4,"需支付10块钱");
            //返回修改的数据行数来判断是否有添加进数据库成功
            int i = pstmt.executeUpdate();
            //删除出场数据
            pstmt = connection.prepareStatement("DELETE FROM carTable WHERE id=?");
            pstmt.setString(1,currentCar);
            pstmt.executeUpdate();
            return i;
        } catch (Exception e){
            System.out.println("数据库查询语句或者数据库数据错误");
            return 0;
        }
    }

    //得到数据库的数据并添加到集合中
    public static void getHistoryData(HashSet<Car> carHashSet){
        try {
            //获取数据库连接
            Connection connection = DBUtil.getDbUtil().connection;
            //得到查询语句
            PreparedStatement pstmt = connection.prepareStatement("SELECT id,timein,timeout,state FROM carHistory");
            //执行查询语句返回结果
            ResultSet result = pstmt.executeQuery();
            //将相应的数据加到集合中
            while (result.next()){
                carHashSet.add(new Car(result.getString("id")
                        ,result.getString("timein")
                        ,result.getString("timeout")
                        ,result.getString("state")));
            }
        } catch (Exception e){
            System.out.println("数据库查询语句或者数据库数据错误");
            e.printStackTrace();
        }
    }
}
